﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net.Mail;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;

namespace MIT.DivAdmin
{
    public partial class DivAdminCalender2 : System.Web.UI.Page
    {

        String Req1;
        String Req2;
        String Req3;
        String NewsDate1;
        String NewsDate2;
        String NewsDate3;
        String NewsTitle1;
        String NewsTitle2;
        String NewsTitle3;
        String NewsDetails1;
        String NewsDetails2;
        String NewsDetails3;


        string[] getlistname = new string[3];
        string[] getlistemail = new string[3];
        string name1 = null;
        string name2 = null;
        string name3 = null;
        string email1 = null;
        string email2 = null;
        string email3 = null;
        string request1 = null;
        string request2 = null;
        string request3 = null;
        string Name = null;
        string Division1 = null;

        String Mode;

        protected void Page_Load(object sender, EventArgs e)
        {
            Name = (String)Session["LoggingInfo"];
            String Position = (String)Session["LoggingPosition"];
            String username = (String)Session["LoggingInfoUsername"];
            Division1 = (String)Session["LoggingDivision"];
            LabelDivAdminName.Text = Name;
            LabelDivAdminPosition.Text = "(" + Position + ")";

            ImageProfile.ImageUrl = "../ProfilePicHandler.ashx?uname=" + username;

            Session["CalLoggingInfo"] = Name;
            Session["CalLoggingPosition"] = Position;
            Session["CalLoggingInfoUsername"] = username;

            ///////////////////Notification Number//////////////////////////////////////////////////
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand ReqMsgNo = new SqlCommand("spMsgCounter", con);
                ReqMsgNo.CommandType = CommandType.StoredProcedure;

                SqlParameter ReqName3 = new SqlParameter("@username", username);
                ReqMsgNo.Parameters.Add(ReqName3);



                con.Open();
                SqlDataReader dr;

                dr = ReqMsgNo.ExecuteReader();

                if (dr.Read())
                {
                    Req1 = Convert.ToString(dr["REQUEST1"]);
                    Req2 = Convert.ToString(dr["REQUEST2"]);
                    Req3 = Convert.ToString(dr["REQUEST3"]);
                }

            }
            if (Req1 == "")
            {
                LabelMsgNo.Text = "";

            }
            else if (Req2 == "")
            {
                LabelMsgNo.Text = "1";
            }
            else if (Req3 == "")
            {
                LabelMsgNo.Text = "2";
            }
            else
            {
                LabelMsgNo.Text = "3";
            }


            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand GetNews = new SqlCommand("spGetNews1", con);
                GetNews.CommandType = CommandType.StoredProcedure;

                SqlParameter News1 = new SqlParameter("@News1", 1);
                GetNews.Parameters.Add(News1);



                con.Open();
                SqlDataReader dr;

                dr = GetNews.ExecuteReader();

                if (dr.Read())
                {
                    NewsDate1 = Convert.ToString(dr["LiveDate"]);
                    NewsTitle1 = Convert.ToString(dr["Title"]);
                    NewsDetails1 = Convert.ToString(dr["Details"]);

                }

            }

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand GetNews = new SqlCommand("spGetNews2", con);
                GetNews.CommandType = CommandType.StoredProcedure;

                SqlParameter News2 = new SqlParameter("@News2", 2);
                GetNews.Parameters.Add(News2);



                con.Open();
                SqlDataReader dr;

                dr = GetNews.ExecuteReader();

                if (dr.Read())
                {
                    NewsDate2 = Convert.ToString(dr["LiveDate"]);
                    NewsTitle2 = Convert.ToString(dr["Title"]);
                    NewsDetails2 = Convert.ToString(dr["Details"]);

                }

            }

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand GetNews = new SqlCommand("spGetNews3", con);
                GetNews.CommandType = CommandType.StoredProcedure;

                SqlParameter News3 = new SqlParameter("@News3", 3);
                GetNews.Parameters.Add(News3);



                con.Open();
                SqlDataReader dr;

                dr = GetNews.ExecuteReader();

                if (dr.Read())
                {
                    NewsDate3 = Convert.ToString(dr["LiveDate"]);
                    NewsTitle3 = Convert.ToString(dr["Title"]);
                    NewsDetails3 = Convert.ToString(dr["Details"]);
                }

            }



            LabelDetails1.Text = NewsDetails1;
            LabelDetails2.Text = NewsDetails2;
            LabelDetails3.Text = NewsDetails3;

            LabelNews1.Text = NewsTitle1;
            LabelNews2.Text = NewsTitle2;
            LabelNews3.Text = NewsTitle3;

            LabelDate1.Text = NewsDate1;
            LabelDate2.Text = NewsDate2;
            LabelDate3.Text = NewsDate3;

            if (!IsPostBack)
            {
                CheckBoxListInterviewPanel.SelectedIndex = 0;
            }




        }

        protected void ButtonLogout_Click(object sender, EventArgs e)
        {
            Response.Redirect("../Login/SignIn.aspx");
        }

        protected void ButtonStep2_Click1(object sender, EventArgs e)
        {
            Mode = (String)Session["Mode"];

            if (Mode == "First Interview")
            {
                int InterviewNo = (int)Session["InterviewNo"];
                String InterviewDate = (String)Session["InterviewDate"];
                int i = 0;
                foreach (ListItem li in CheckBoxListInterviewPanel.Items)
                {


                    if (li.Selected)
                    {
                        if (i == 3)
                        {
                            LabelValidation.Text = "Select only three Interviwers";
                            goto EndofLoop;
                        }
                        getlistname[i] = li.Text;
                        
                        
                        i++;
                    }
                }
            
                if (getlistname[2] == null)
                {
                    LabelValidation.Text = "Select aleast three Interviwers";
                }

                else
                {
                    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(CS))
                    {
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 1////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[0] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName1", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name1", getlistname[0]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name1 = dr["USERNAME"].ToString();
                            }
                            con.Close();


                            SqlCommand com2 = new SqlCommand("spGetEmail1", con);
                            com2.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname2 = new SqlParameter("@UserName1", name1);
                            com2.Parameters.Add(sendname2);



                            con.Open();
                            SqlDataReader dr1;

                            dr1 = com2.ExecuteReader();

                            if (dr1.Read())
                            {
                                email1 = dr1["EMAIL"].ToString();
                            }
                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name1);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name1);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name1);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name1);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@UsernameReq2", name1);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@UsernameReq3", name1);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[0] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }


                            //////////////////////////////////////////////////////////////////////////////////////////
                            //////////////////////////////////////////////////////////////////////////////////////////

                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email1);
                                    //mm.From = new MailAddress();
                                    //mm.To.Add();
                                    mm.Subject = "Interview Panel Invitation";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }


                            con.Close();
                        }
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 2////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[1] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName2", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name2", getlistname[1]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name2 = dr["USERNAME"].ToString();
                            }
                            con.Close();

                            SqlCommand com2 = new SqlCommand("spGetEmail2", con);
                            com2.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname2 = new SqlParameter("@UserName2", name2);
                            com2.Parameters.Add(sendname2);



                            con.Open();
                            SqlDataReader dr1;

                            dr1 = com2.ExecuteReader();

                            if (dr1.Read())
                            {
                                email2 = dr1["EMAIL"].ToString();
                            }

                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name2);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name2);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name2);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name2);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq2", name2);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq3", name2);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[1] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }


                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email2);
                                    mm.Subject = "Subject Test1";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }

                            con.Close();
                        }

                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 3////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[2] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName3", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name3", getlistname[2]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name3 = dr["USERNAME"].ToString();
                            }
                            con.Close();


                            SqlCommand com3 = new SqlCommand("spGetEmail3", con);
                            com3.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname3 = new SqlParameter("@UserName3", name3);
                            com3.Parameters.Add(sendname3);



                            con.Open();
                            SqlDataReader dr2;

                            dr2 = com3.ExecuteReader();

                            if (dr2.Read())
                            {
                                email3 = dr2["EMAIL"].ToString();


                            }

                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name3);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name3);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name3);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name3);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq2", name3);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("First Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq3", name3);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[2] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }

                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email3);
                                    //mm.From = new MailAddress();
                                    //mm.To.Add();
                                    mm.Subject = "Subject Test1";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }

                        }
                    }
                    string CSs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(CSs))
                    {
                        SqlCommand cmd1 = new SqlCommand("spAddInterview2", con);
                        cmd1.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd1.Parameters.AddWithValue("@INTERVIEW_NO", InterviewNo);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER1", getlistname[0]);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER2", getlistname[1]);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER3", getlistname[2]);
                        cmd1.Parameters.AddWithValue("@USERNAME1", name1);
                        cmd1.Parameters.AddWithValue("@USERNAME2", name2);
                        cmd1.Parameters.AddWithValue("@USERNAME3", name3);
                        con.Close();
                        con.Open();
                        cmd1.ExecuteNonQuery();

                        LabelInterviewStatus.Text = "Schedule Successful";

                        Response.Redirect("DivAdminScheduledInterview.aspx");
                    }
                EndofPage: ;
                } 
            }

            else if (Mode == "Second Interview")
            {
                int InterviewNo = (int)Session["InterviewNo"];
                String InterviewDate = (String)Session["InterviewDate"];
                int i = 0;
                foreach (ListItem li in CheckBoxListInterviewPanel.Items)
                {


                    if (li.Selected)
                    {

                        getlistname[i] = li.Text;
                        i++;
                        if (i == 3)
                        {
                            LabelValidation.Text = "Select only three Interviwers";
                        }
                        
                    }
                }
            
                if (getlistname[2] == null)
                {
                    LabelValidation.Text = "Select aleast three Interviwers";
                }

                else
                {
                    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(CS))
                    {
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 1////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[0] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName1", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name1", getlistname[0]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name1 = dr["USERNAME"].ToString();
                            }
                            con.Close();


                            SqlCommand com2 = new SqlCommand("spGetEmail1", con);
                            com2.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname2 = new SqlParameter("@UserName1", name1);
                            com2.Parameters.Add(sendname2);



                            con.Open();
                            SqlDataReader dr1;

                            dr1 = com2.ExecuteReader();

                            if (dr1.Read())
                            {
                                email1 = dr1["EMAIL"].ToString();
                            }
                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name1);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name1);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name1);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name1);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@UsernameReq2", name1);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@UsernameReq3", name1);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[0] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }


                            //////////////////////////////////////////////////////////////////////////////////////////
                            //////////////////////////////////////////////////////////////////////////////////////////

                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email1);
                                    //mm.From = new MailAddress();
                                    //mm.To.Add();
                                    mm.Subject = "Interview Panel Invitation";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }


                            con.Close();
                        }
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 2////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[1] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName2", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name2", getlistname[1]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name2 = dr["USERNAME"].ToString();
                            }
                            con.Close();

                            SqlCommand com2 = new SqlCommand("spGetEmail2", con);
                            com2.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname2 = new SqlParameter("@UserName2", name2);
                            com2.Parameters.Add(sendname2);



                            con.Open();
                            SqlDataReader dr1;

                            dr1 = com2.ExecuteReader();

                            if (dr1.Read())
                            {
                                email2 = dr1["EMAIL"].ToString();
                            }

                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name2);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name2);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name2);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name2);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq2", name2);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq3", name2);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[1] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }


                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email2);
                                    mm.Subject = "Subject Test1";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }

                            con.Close();
                        }

                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 3////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[2] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName3", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name3", getlistname[2]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name3 = dr["USERNAME"].ToString();
                            }
                            con.Close();


                            SqlCommand com3 = new SqlCommand("spGetEmail3", con);
                            com3.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname3 = new SqlParameter("@UserName3", name3);
                            com3.Parameters.Add(sendname3);



                            con.Open();
                            SqlDataReader dr2;

                            dr2 = com3.ExecuteReader();

                            if (dr2.Read())
                            {
                                email3 = dr2["EMAIL"].ToString();


                            }

                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name3);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name3);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name3);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name3);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq2", name3);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("SecondInterview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq3", name3);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[2] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }

                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email3);
                                    //mm.From = new MailAddress();
                                    //mm.To.Add();
                                    mm.Subject = "Subject Test1";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }

                        }
                    }
                    string CSs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(CSs))
                    {
                        SqlCommand cmd1 = new SqlCommand("spAddInterview2Two", con);
                        cmd1.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd1.Parameters.AddWithValue("@INTERVIEW_NO", InterviewNo);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER1", getlistname[0]);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER2", getlistname[1]);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER3", getlistname[2]);
                        cmd1.Parameters.AddWithValue("@USERNAME1", name1);
                        cmd1.Parameters.AddWithValue("@USERNAME2", name2);
                        cmd1.Parameters.AddWithValue("@USERNAME3", name3);
                        con.Close();
                        con.Open();
                        cmd1.ExecuteNonQuery();

                        LabelInterviewStatus.Text = "Schedule Successful";

                        Response.Redirect("DivAdminScheduledInterviewTwo.aspx");
                    }
                EndofPage: ;
                }
            }


            else if (Mode == "Final Interview")
            {
                int InterviewNo = (int)Session["InterviewNo"];
                String InterviewDate = (String)Session["InterviewDate"];
                int i = 0;
                foreach (ListItem li in CheckBoxListInterviewPanel.Items)
                {


                    if (li.Selected)
                    {

                        getlistname[i] = li.Text;
                        i++;
                        if (i == 3)
                        {
                            LabelValidation.Text = "Select only three Interviwers";
                        }
                        
                    }
                }
            
                if (getlistname[2] == null)
                {
                    LabelValidation.Text = "Select aleast three Interviwers";
                }

                else
                {
                    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(CS))
                    {
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 1////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[0] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName1", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name1", getlistname[0]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name1 = dr["USERNAME"].ToString();
                            }
                            con.Close();


                            SqlCommand com2 = new SqlCommand("spGetEmail1", con);
                            com2.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname2 = new SqlParameter("@UserName1", name1);
                            com2.Parameters.Add(sendname2);



                            con.Open();
                            SqlDataReader dr1;

                            dr1 = com2.ExecuteReader();

                            if (dr1.Read())
                            {
                                email1 = dr1["EMAIL"].ToString();
                            }
                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name1);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name1);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name1);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name1);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@UsernameReq2", name1);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@UsernameReq3", name1);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[0] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }


                            //////////////////////////////////////////////////////////////////////////////////////////
                            //////////////////////////////////////////////////////////////////////////////////////////

                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email1);
                                    //mm.From = new MailAddress();
                                    //mm.To.Add();
                                    mm.Subject = "Interview Panel Invitation";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }


                            con.Close();
                        }
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 2////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[1] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName2", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name2", getlistname[1]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name2 = dr["USERNAME"].ToString();
                            }
                            con.Close();

                            SqlCommand com2 = new SqlCommand("spGetEmail2", con);
                            com2.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname2 = new SqlParameter("@UserName2", name2);
                            com2.Parameters.Add(sendname2);



                            con.Open();
                            SqlDataReader dr1;

                            dr1 = com2.ExecuteReader();

                            if (dr1.Read())
                            {
                                email2 = dr1["EMAIL"].ToString();
                            }

                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name2);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name2);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name2);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name2);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq2", name2);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq3", name2);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[1] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }


                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email2);
                                    mm.Subject = "Subject Test1";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }

                            con.Close();
                        }

                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        ///////////////////////////////////////////////////////////////////////////////////////////////////////////
                        /////////////////User 3////////////////////////////////////////////////////////////////////////////////////
                        if (getlistname[2] != null)
                        {
                            SqlCommand com = new SqlCommand("spGetName3", con);
                            com.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname1 = new SqlParameter("@Name3", getlistname[2]);
                            com.Parameters.Add(sendname1);



                            con.Open();
                            SqlDataReader dr;

                            dr = com.ExecuteReader();

                            if (dr.Read())
                            {
                                name3 = dr["USERNAME"].ToString();
                            }
                            con.Close();


                            SqlCommand com3 = new SqlCommand("spGetEmail3", con);
                            com3.CommandType = CommandType.StoredProcedure;

                            SqlParameter sendname3 = new SqlParameter("@UserName3", name3);
                            com3.Parameters.Add(sendname3);



                            con.Open();
                            SqlDataReader dr2;

                            dr2 = com3.ExecuteReader();

                            if (dr2.Read())
                            {
                                email3 = dr2["EMAIL"].ToString();


                            }

                            /////////////////////////////////////////////////////////////////////////////////////////////
                            ////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom1 = new SqlCommand("spRequestchecker1", con);
                            ReqCom1.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName1 = new SqlParameter("@username", name3);
                            ReqCom1.Parameters.Add(ReqName1);



                            con.Open();
                            SqlDataReader drreq1;

                            drreq1 = ReqCom1.ExecuteReader();

                            if (drreq1.Read())
                            {
                                request1 = drreq1["REQUEST1"].ToString();
                            }
                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom2 = new SqlCommand("spRequestchecker2", con);
                            ReqCom2.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName2 = new SqlParameter("@username", name3);
                            ReqCom2.Parameters.Add(ReqName2);



                            con.Open();
                            SqlDataReader drreq2;

                            drreq2 = ReqCom2.ExecuteReader();

                            if (drreq2.Read())
                            {
                                request2 = drreq2["REQUEST2"].ToString();
                            }
                            //////////////////////////////////////////////////////////////////////////////////////////////
                            con.Close();


                            SqlCommand ReqCom3 = new SqlCommand("spRequestchecker3", con);
                            ReqCom3.CommandType = CommandType.StoredProcedure;

                            SqlParameter ReqName3 = new SqlParameter("@username", name3);
                            ReqCom3.Parameters.Add(ReqName3);



                            con.Open();
                            SqlDataReader drreq3;

                            drreq3 = ReqCom3.ExecuteReader();

                            if (drreq3.Read())
                            {
                                request3 = drreq3["REQUEST3"].ToString();
                            }

                            if (request1 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest1", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@UsernameReq1", name3);
                                cmd.Parameters.AddWithValue("@Sender1", Name);
                                cmd.Parameters.AddWithValue("@DIVISION1", Division1);
                                cmd.Parameters.AddWithValue("@Req1", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request2 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest2", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req2", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq2", name3);
                                cmd.Parameters.AddWithValue("@DIVISION2", Division1);
                                cmd.Parameters.AddWithValue("@Sender2", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }

                            else if (request3 == "")
                            {
                                SqlCommand cmd = new SqlCommand("spAddRequest3", con);
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@Req3", ("Final Interview.Interview No. " + InterviewNo + ". You have an interview panel invitation on " + InterviewDate));
                                cmd.Parameters.AddWithValue("@UsernameReq3", name3);
                                cmd.Parameters.AddWithValue("@DIVISION3", Division1);
                                cmd.Parameters.AddWithValue("@Sender3", Name);
                                con.Close();
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                            else
                            {
                                LabelRequestMax.Text = getlistname[2] + " has too many requests. Pick Another";
                                goto EndofPage;
                            }

                            if (Page.IsValid)
                                try
                                {
                                    MailMessage mm = new MailMessage("cms.mit2015@gmail.com", email3);
                                    //mm.From = new MailAddress();
                                    //mm.To.Add();
                                    mm.Subject = "Subject Test1";

                                    mm.Body = "Hello Test 1";

                                    mm.IsBodyHtml = true;

                                    SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
                                    smtpClient.EnableSsl = true;
                                    smtpClient.Credentials = new System.Net.NetworkCredential()
                                    {
                                        UserName = "cms.mit2015@gmail.com",
                                        Password = "millenium2015"
                                    };
                                    smtpClient.Send(mm);


                                }

                                catch (Exception ex)
                                {
                                    Response.Write("Could not send the e-mail - error: " + ex.Message);
                                }

                        }
                    }
                    string CSs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(CSs))
                    {
                        SqlCommand cmd1 = new SqlCommand("spAddInterview2Three", con);
                        cmd1.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd1.Parameters.AddWithValue("@INTERVIEW_NO", InterviewNo);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER1", getlistname[0]);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER2", getlistname[1]);
                        cmd1.Parameters.AddWithValue("@INTERVIEWER3", getlistname[2]);
                        cmd1.Parameters.AddWithValue("@USERNAME1", name1);
                        cmd1.Parameters.AddWithValue("@USERNAME2", name2);
                        cmd1.Parameters.AddWithValue("@USERNAME3", name3);
                        con.Close();
                        con.Open();
                        cmd1.ExecuteNonQuery();

                        LabelInterviewStatus.Text = "Schedule Successful";

                        Response.Redirect("DivAdminScheduledInterviewFinal.aspx");
                    }
                EndofPage: ;
                }
            }
        EndofLoop: ;
        }
    }
}